package com.jianyun.jyzs.dao;

import android.content.Context;

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.Where;
import com.jianyun.jyzs.bean.Sign;
import com.jianyun.jyzs.db.DatabaseHelper;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jianyun.jyzs.utils.RceLog;

@SuppressWarnings("unchecked")
public class SignDao {
    private Dao<Sign, Integer> dao;
    private DatabaseHelper helper;

	public SignDao(Context context) {
		try{
            helper = DatabaseHelper.getHelper(context);
            dao = helper.getDao(Sign.class);
        }catch (Exception e){  
            e.printStackTrace();  
        }  
	}
	/**
	 * @return
	 */
	public List<Sign> getListData(String userId, long startsize,long pagesize,String dateTime,String enterpriseCode) {
		try {
			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
			builder.orderBy("id", false);
			Where<Sign, Integer> where = builder.where();
			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
			where.and().like("dateTime", "%"+dateTime+"%");
			builder.offset(startsize);//表示查询的起始位置
			builder.limit(pagesize);//表示总共获取的对象数量
			return builder.query();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ArrayList<Sign>();
	}
	/**
	 */
	public List<String[]> getListData(String userId,String yyyy_mm,String enterpriseCode) {
		List<String[]> list = new ArrayList<String[]>();
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("select dt,count(dateTime) as num from ");
			sql.append(Sign.TABLE_NAME);
			sql.append(" where userId = '"+userId+"' and enterpriseCode = '"+enterpriseCode+"' ");
			sql.append(" and dt like '"+yyyy_mm+"%' ");
			sql.append(" group by dt ");
			try {
				GenericRawResults<Object[]> rawResults =dao.queryRaw(sql.toString(),
						new DataType[] { DataType.STRING, DataType.INTEGER });
						for (Object[] resultArray : rawResults) {
							list.add(new String[]{String.valueOf(resultArray[0]).substring(8),String.valueOf(resultArray[1])});
						}
						rawResults.close();
			} catch (Exception e) {
				e.printStackTrace();
			}		
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<Sign> getListByDate(String userId,String yyyy_mm,String enterpriseCode){
		List<Sign> query = new ArrayList<>();
		QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
		try {
			builder.where().eq("userId",userId).and().eq("enterpriseCode",enterpriseCode).and().eq("dt",yyyy_mm);
			query = builder.query();
			return  query;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;

	}

	/**
	 * 获取当天最早签到时间
	 * @return
	 */
	public Sign getStartDate(String userId, String dateTime,String enterpriseCode) {
		List<Sign> list = null;
		try {
			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
			builder.orderBy("id", false);
//			builder.orderBy("morningTimeValid", true).orderBy("morningPlaceValid", true);
			Where<Sign, Integer> where = builder.where();
			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
			where.and().eq("morningTimeValid", 0).and().eq("morningPlaceValid", 0);
			where.and().like("dateTime", "%"+dateTime+"%");
			builder.offset(0);//表示查询的起始位置
			builder.limit(1);//表示总共获取的对象数量
			list = builder.query();
			RceLog.d("lml","查询result"+list.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		
		try {
			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
			builder.orderBy("id", false);
//			builder.orderBy("morningTimeValid", true).orderBy("morningPlaceValid", true);
			Where<Sign, Integer> where = builder.where();
			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
			where.and().ne("morningTimeValid", -1).and().ne("morningPlaceValid", -1);
//			where.and().eq("morningPlaceValid", 1);
			where.and().like("dateTime", "%"+dateTime+"%");
			builder.offset(0);//表示查询的起始位置
			builder.limit(1);//表示总共获取的对象数量
			list = builder.query();
			RceLog.d("lml","查询result"+list.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}

//		try {
//			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
//			builder.orderBy("id", false);
////			builder.orderBy("morningTimeValid", true).orderBy("morningPlaceValid", true);
//			Where<Sign, Integer> where = builder.where();
//			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
//			where.and().ne("morningPlaceValid", -1);
//			where.and().like("dateTime", "%"+dateTime+"%");
//			builder.offset(0);//表示查询的起始位置
//			builder.limit(1);//表示总共获取的对象数量
//			list = builder.query();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}/**
	 * 获取当天最晚签到时间
	 * @return
	 */
	public Sign getEndDate(String userId, String dateTime,String enterpriseCode) {
		List<Sign> list = null;
		try {
			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
			builder.orderBy("id", false);
			Where<Sign, Integer> where = builder.where();
			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
			where.and().eq("afternoonTimeValid", 0).and().eq("afternoonPlaceValid", 0);
			where.and().like("dateTime", "%"+dateTime+"%");
			builder.offset(0);//表示查询的起始位置
			builder.limit(1);//表示总共获取的对象数量
			list = builder.query();
			RceLog.d("lml","查询result"+list.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}

		try {
			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
			builder.orderBy("id", false);
			Where<Sign, Integer> where = builder.where();
			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
			where.and().ne("afternoonTimeValid", -1).and().ne("afternoonPlaceValid", -1);
			where.and().like("dateTime", "%"+dateTime+"%");
			builder.offset(0);//表示查询的起始位置
			builder.limit(1);//表示总共获取的对象数量
			list = builder.query();
			RceLog.d("lml","查询result"+list.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}

//		try {
//			QueryBuilder<Sign, Integer> builder = dao.queryBuilder();
//			builder.orderBy("id", false);
//			Where<Sign, Integer> where = builder.where();
//			where.eq("userId", userId).and().eq("enterpriseCode", enterpriseCode);
//			where.and().eq("afternoonPlaceValid", 0);
//			where.and().like("dateTime", "%"+dateTime+"%");
//			builder.offset(0);//表示查询的起始位置
//			builder.limit(1);//表示总共获取的对象数量
//			list = builder.query();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}
	/**
	 * 保存
	 * @return
	 */
	public synchronized void insert(Sign sign,String enterpriseCode) {
		RceLog.d("lml","保存数据"+sign.toString());

		try {
			sign.setEnterpriseCode(enterpriseCode);
			dao.createOrUpdate(sign);
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
	}
	/**
	 * 删除
	 * @return
	 */
	public synchronized void delete(String userId,String enterpriseCode,String yyyy) {
//		StringBuffer sql = new StringBuffer("");
//		sql.append("delete ");
//		sql.append(Sign.TABLE_NAME);
//		sql.append(" where userId = '"+userId+"' and enterpriseCode = '"+enterpriseCode+"' ");
//		sql.append(" and dt like '"+yyyy+"%' ");
		try {
//			dao.executeRaw(sql.toString());
    		DeleteBuilder<Sign, Integer> deleteBuilder = dao.deleteBuilder();
    		deleteBuilder.where().eq("userId", userId).and().eq("enterpriseCode", enterpriseCode).and().like("dt", yyyy+"%");
    		deleteBuilder.delete();
		} catch (Exception e) {
			e.printStackTrace();
		}	
		
	}
}